import xml.utils.*;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.rowset.*;
import javax.swing.JOptionPane;

import com.sun.rowset.*;

public class AccessDBImpl extends UnicastRemoteObject implements AccessDBInterface {

	public Connection con;
	public Statement stmt;

	public AccessDBImpl() throws RemoteException {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con = DriverManager.getConnection("jdbc:odbc:WarehouseDatabase");
			stmt = con.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public WebRowSet GetTable(String tableName) throws RemoteException {
		WebRowSet rs = null;
		try {
			String sql = "select * from " + tableName;
			ResultSet r = stmt.executeQuery(sql);
			rs=new WebRowSetImpl();
			rs.populate(r);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	public Connection getCon() {
		return con;
	}

	public void setCon(Connection con) {
		this.con = con;
	}

	public Statement getStmt() {
		return stmt;
	}

	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}

	public int ExecUpdateSQL(String sql) throws RemoteException {
		try {
			int x = stmt.executeUpdate(sql);
			return x;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public void stopConnection() throws SQLException, RemoteException {
		con.close();
	}

	public WebRowSet ExecQuerySQL(String sql) throws RemoteException {
		// TODO Auto-generated method stub
		WebRowSet rs = null;
		
		try {
			ResultSet r= stmt.executeQuery(sql);
			rs=new WebRowSetImpl();
			rs.populate(r);
			return rs;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}

	@Override
	public int checkLoginInformation(String strUserID, String strPassword,
			int iServer) throws RemoteException {
		// TODO Auto-generated method stub
		WebRowSet wrs = null;
		
		try {
			ResultSet r = stmt.executeQuery("select ut.UserTypeID, ut.WarehouseID from User u, UserType ut " +
											"where u.UserTypeID = ut.UserTypeID and "+
												"u.UserID = '" + strUserID + "' and " + 
												"u.Password = '" + strPassword + "'");			
			wrs = new WebRowSetImpl();
			wrs.populate(r);
			rsTableModel rs = new rsTableModel(wrs);
			
			if (rs.getRowCount() > 0) {
				if (rs.getValueAt(0, 0).toString().equals("UT005")) {
					return 1; //Quan ly
				}
				else {
					String strServer = rs.getValueAt(0, 1).toString().substring(1);					
					if(Integer.parseInt(strServer) == iServer) {
						String strUserType = rs.getValueAt(0, 0).toString();
						if(strUserType.equals("UT001") || strUserType.equals("UT009") || strUserType.equals("UT010"))
							return 2; //Admin
						if(strUserType.equals("UT002") || strUserType.equals("UT003") || strUserType.equals("UT004"))
							return 3; //Thu kho
						if(strUserType.equals("UT006") || strUserType.equals("UT007") || strUserType.equals("UT008"))
							return 4; //NV giao hang
					}
					else {
						return -1; //Wrong server
					}
				}
			}
			else {
				return 0; //Wrong UserID and Password
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
	}
}  